
insert overwrite table jms_dm.dm_terminal_deliver_punctuality_rate_send_sum_dt partition (dt)
select  end_agent_code,  --末端代理区code
       max(end_agent_name) as end_agent_name, --末端代理区
        end_center_code,  --末端中心code
       max(end_center_name) as end_center_name, --末端中心名字
       end_city_id,  --城市id
       max(end_city_name)as end_city_name, --城市名字
       final_deliver_network_code,  --派件网点code
       max(final_deliver_network_name) as final_deliver_network_name,  --派件网点名字
       end_franchisee_code, --加盟商code
       max(end_franchisee_name) as end_franchisee_name,-- 加盟商
       order_source_code,  --订单来源code
       max(order_source_name) as order_source_name, --订单来源
       max(is_common) as is_common,  --是否共配
       count(1) as need_deliver_count,  --应派件总量
       sum(is_deliver_punctuality) as  punctuality_sum, --及时派件总量
       max(if(deliver_shift=1,planned_departure_time,null)) as one_planned_departure_time,  --一班规划接件时间
       max(if(deliver_shift=1,plan_warehouse_end_time ,null)) as one_plan_warehouse_end_time,  --一班规划出仓时间
       sum(if(deliver_shift=1,1,0)) as one_need_deliver_count,  -- 一班应出仓总量
       sum(if(deliver_shift=1 and is_deliver_punctuality=1,1,0)) as one_punctuality_sum,  --一班准点出仓量
       sum(if(deliver_shift=1 and delay_minutes is not null and  delay_minutes<=30,1,0)) as one_delay_30_minutes,  -- 一班延误30分钟量
       sum(if(deliver_shift=1 and delay_minutes is not null and  delay_minutes>30 and delay_minutes<=60,1,0)) as one_delay_60_minutes,  --一班延误30分钟量
       sum(if(deliver_shift=1 and delay_minutes is not null and  delay_minutes>60 and delay_minutes<=120,1,0)) as one_delay_60_minutes,  -- 一班延误120分钟总量
       max(if(deliver_shift=2,planned_departure_time,null)) as two_planned_departure_time,  --一班规划接件时间
       max(if(deliver_shift=2,plan_warehouse_end_time ,null)) as two_plan_warehouse_end_time,  --一班规划出仓时间
       sum(if(deliver_shift=2,1,0)) as two_need_deliver_count,  -- 一班应出仓总量
       sum(if(deliver_shift=2 and is_deliver_punctuality=1,1,0)) as two_punctuality_sum,  --一班准点出仓量
       sum(if(deliver_shift=2 and delay_minutes is not null and  delay_minutes<=30,1,0)) as two_delay_30_minutes,  -- 一班延误30分钟量
       sum(if(deliver_shift=2 and delay_minutes is not null and  delay_minutes>30 and delay_minutes<=60,1,0)) as two_delay_60_minutes,  --一班延误30分钟量
       sum(if(deliver_shift=2 and delay_minutes is not null and  delay_minutes>60 and delay_minutes<=120,1,0)) as two_delay_60_minutes,  -- 一班延误120分钟总量
       is_township,  --1乡镇 0 非乡镇
       dt as date_time, --业务日期
              -- 增加三派
       max(if(deliver_shift=3,planned_departure_time,null)) as three_planned_departure_time,  --一班规划接件时间
       max(if(deliver_shift=3,plan_warehouse_end_time ,null)) as three_plan_warehouse_end_time,  --一班规划出仓时间
       sum(if(deliver_shift=3,1,0)) as three_need_deliver_count,  -- 一班应出仓总量
       sum(if(deliver_shift=3 and is_deliver_punctuality=1,1,0)) as three_punctuality_sum,  --一班准点出仓量
       sum(if(deliver_shift=3 and delay_minutes is not null and  delay_minutes<=30,1,0)) as three_delay_30_minutes,  -- 一班延误30分钟量
       sum(if(deliver_shift=3 and delay_minutes is not null and  delay_minutes>30 and delay_minutes<=60,1,0)) as three_delay_60_minutes,  --一班延误30分钟量
       sum(if(deliver_shift=3 and delay_minutes is not null and  delay_minutes>60 and delay_minutes<=120,1,0)) as three_delay_60_minutes,  -- 一班延误120分钟总量
       final_sign_user_code,
       max(final_sign_user_name) as final_sign_user_name,
       dt
from jms_dm.dm_terminal_deliver_punctuality_rate_detail_dt
where dt >= date_sub('{{ execution_date  | cst_ds }}',14)
and dt <= '{{ execution_date  | cst_ds }}'
group by dt,end_agent_code,
         end_center_code,
         end_city_id,
         end_franchisee_code,
         final_deliver_network_code,
         order_source_code,
         is_township,
         final_sign_user_code
distribute by dt,pmod(hash(rand()),5);
